library(tidyverse)
library(lubridate)

Here is our first task:


Part 1: Assemble the project cohort

The project goal is to identify patients seen for drug overdose, determine if they had an active opioid at the start of the encounter, and if they had any readmissions for drug overdose.

Your task is to assemble the study cohort by identifying encounters that meet the following criteria:

  1. The patient’s visit is an encounter for drug overdose
  2. The hospital encounter occurs after July 15, 1999
  3. The patient’s age at time of encounter is between 18 and 35 (Patient is considered to be 35 until turning 36)

Sounds great. Let’s start by taking a look at the data.

allergies <- read_csv("datasets/allergies.csv")
Parsed with column specification:
cols(
  START = col_date(format = ""),
  STOP = col_date(format = ""),
  PATIENT = col_character(),
  ENCOUNTER = col_character(),
  CODE = col_double(),
  DESCRIPTION = col_character()
)
allergies

encounters <- read_csv("datasets/encounters.csv")
Parsed with column specification:
cols(
  Id = col_character(),
  START = col_datetime(format = ""),
  STOP = col_datetime(format = ""),
  PATIENT = col_character(),
  PROVIDER = col_character(),
  ENCOUNTERCLASS = col_character(),
  CODE = col_double(),
  DESCRIPTION = col_character(),
  COST = col_double(),
  REASONCODE = col_double(),
  REASONDESCRIPTION = col_character()
)
encounters

medications <- read_csv("datasets/medications.csv")
Parsed with column specification:
cols(
  START = col_date(format = ""),
  STOP = col_date(format = ""),
  PATIENT = col_character(),
  ENCOUNTER = col_character(),
  CODE = col_double(),
  DESCRIPTION = col_character(),
  COST = col_double(),
  DISPENSES = col_double(),
  TOTALCOST = col_double(),
  REASONCODE = col_double(),
  REASONDESCRIPTION = col_character()
)
medications

patients <- read_csv("datasets/patients.csv")
Parsed with column specification:
cols(
  .default = col_character(),
  BIRTHDATE = col_date(format = ""),
  DEATHDATE = col_date(format = ""),
  ZIP = col_double()
)
See spec(...) for full column specifications.
patients

procedures <- read_csv("datasets/procedures.csv")
Parsed with column specification:
cols(
  DATE = col_date(format = ""),
  PATIENT.x = col_character(),
  ENCOUNTER = col_character(),
  CODE.x = col_character(),
  DESCRIPTION.x = col_character(),
  COST.x = col_double(),
  REASONCODE.x = col_double(),
  REASONDESCRIPTION.x = col_character()
)
procedures

Ok, we are chiefly interested in the encounters table, and basically want to filter it based on the specifications given in the task. Let’s start by filtering the encounters by drug overdose. Looking at the data dictionary sheet for the encounters table, we can see that the REASONCODE column are SNOMED-CT codes.

We can lookup the code for a drug overdose here: https://browser.ihtsdotools.org/, which has the code as 55680006.

drug_overdoses <- filter(encounters, REASONCODE == 55680006)
drug_overdoses

Great, now we just need to filter for encounters that occur after July 15, 1999.

The encounters table has two column that represent the date of the encounter. START and STOP, further clarification would be neccessary to determine if the task is to find encounters that begin after 07/15/1999 or end at that date. For the purposes of this exercise, we’ll go with encounters that begin after that date due to the term occur in the specification.

after_date <- filter(drug_overdoses, START > "1999-07-15")
arrange(after_date, START)

Now we’re concerned with encounters with patients between the ages of 18 and 35; we’ll need to join the patients table to handle that.

with_patients <- inner_join(after_date, patients, c("PATIENT" = "Id"))
with_patients

Based upon the wording in the specifications, the patient’s age must be greater than or equal to 18 at the start of an encounter and less than or equal to 35 at the end of the encounter.

Let’s make sure that there are no encounters in our table that has not ended, because a patient could age to 36 by the time the encounter is over.

not_ended <- drop_na(with_patients, STOP)
not_ended

Turns out we’re ok. Let’s do the filtering now. First we’ll need to calculate the age of the patient at the start and end of the encounter.

not_ended$AGEATSTART <- as.period(interval(not_ended$BIRTHDATE, not_ended$START))$year
not_ended$AGEATSTOP <- as.period(interval(not_ended$BIRTHDATE, not_ended$STOP))$year
select(not_ended, Id, AGEATSTART, AGEATSTOP)
aged <- filter(not_ended, AGEATSTART >= 18 & AGEATSTOP <= 35)
aged

That finishes up the first task.

LS0tCnRpdGxlOiAiQ0hRQSBBbmFseXN0IHRha2UtaG9tZSB0YXNrIgpvdXRwdXQ6IGh0bWxfbm90ZWJvb2sKLS0tCgpgYGB7cn0KbGlicmFyeSh0aWR5dmVyc2UpCmxpYnJhcnkobHVicmlkYXRlKQpgYGAKCkhlcmUgaXMgb3VyIGZpcnN0IHRhc2s6CgotLS0KCiMjIFBhcnQgMTogQXNzZW1ibGUgdGhlIHByb2plY3QgY29ob3J0ClRoZSBwcm9qZWN0IGdvYWwgaXMgdG8gaWRlbnRpZnkgcGF0aWVudHMgc2VlbiBmb3IgZHJ1ZyBvdmVyZG9zZSwgZGV0ZXJtaW5lIGlmIHRoZXkgaGFkIGFuIGFjdGl2ZSBvcGlvaWQgYXQgdGhlIHN0YXJ0IG9mIHRoZSBlbmNvdW50ZXIsIGFuZCBpZiB0aGV5IGhhZCBhbnkgcmVhZG1pc3Npb25zIGZvciBkcnVnIG92ZXJkb3NlLgoKWW91ciB0YXNrIGlzIHRvIGFzc2VtYmxlIHRoZSBzdHVkeSBjb2hvcnQgYnkgaWRlbnRpZnlpbmcgZW5jb3VudGVycyB0aGF0IG1lZXQgdGhlIGZvbGxvd2luZyBjcml0ZXJpYToKCjEuIFRoZSBwYXRpZW504oCZcyB2aXNpdCBpcyBhbiBlbmNvdW50ZXIgZm9yIGRydWcgb3ZlcmRvc2UKMi4gVGhlIGhvc3BpdGFsIGVuY291bnRlciBvY2N1cnMgYWZ0ZXIgSnVseSAxNSwgMTk5OQozLiBUaGUgcGF0aWVudOKAmXMgYWdlIGF0IHRpbWUgb2YgZW5jb3VudGVyIGlzIGJldHdlZW4gMTggYW5kIDM1IChQYXRpZW50IGlzIGNvbnNpZGVyZWQgdG8gYmUgMzUgdW50aWwgdHVybmluZyAzNikKCi0tLQoKU291bmRzIGdyZWF0LiBMZXQncyBzdGFydCBieSB0YWtpbmcgYSBsb29rIGF0IHRoZSBkYXRhLgoKYGBge3J9CmFsbGVyZ2llcyA8LSByZWFkX2NzdigiZGF0YXNldHMvYWxsZXJnaWVzLmNzdiIpCmFsbGVyZ2llcwoKZW5jb3VudGVycyA8LSByZWFkX2NzdigiZGF0YXNldHMvZW5jb3VudGVycy5jc3YiKQplbmNvdW50ZXJzCgptZWRpY2F0aW9ucyA8LSByZWFkX2NzdigiZGF0YXNldHMvbWVkaWNhdGlvbnMuY3N2IikKbWVkaWNhdGlvbnMKCnBhdGllbnRzIDwtIHJlYWRfY3N2KCJkYXRhc2V0cy9wYXRpZW50cy5jc3YiKQpwYXRpZW50cwoKcHJvY2VkdXJlcyA8LSByZWFkX2NzdigiZGF0YXNldHMvcHJvY2VkdXJlcy5jc3YiKQpwcm9jZWR1cmVzCmBgYAoKT2ssIHdlIGFyZSBjaGllZmx5IGludGVyZXN0ZWQgaW4gdGhlIGBlbmNvdW50ZXJzYCB0YWJsZSwgYW5kIGJhc2ljYWxseSB3YW50IHRvIGZpbHRlciBpdCBiYXNlZCBvbiB0aGUgc3BlY2lmaWNhdGlvbnMgZ2l2ZW4gaW4gdGhlIHRhc2suIExldCdzIHN0YXJ0IGJ5IGZpbHRlcmluZyB0aGUgZW5jb3VudGVycyBieSBkcnVnIG92ZXJkb3NlLiBMb29raW5nIGF0IHRoZSBkYXRhIGRpY3Rpb25hcnkgc2hlZXQgZm9yIHRoZSBgZW5jb3VudGVyc2AgdGFibGUsIHdlIGNhbiBzZWUgdGhhdCB0aGUgYFJFQVNPTkNPREVgIGNvbHVtbiBhcmUgU05PTUVELUNUIGNvZGVzLgoKV2UgY2FuIGxvb2t1cCB0aGUgY29kZSBmb3IgYSBkcnVnIG92ZXJkb3NlIGhlcmU6IGh0dHBzOi8vYnJvd3Nlci5paHRzZG90b29scy5vcmcvLCB3aGljaCBoYXMgdGhlIGNvZGUgYXMgYDU1NjgwMDA2YC4KCmBgYHtyfQpkcnVnX292ZXJkb3NlcyA8LSBmaWx0ZXIoZW5jb3VudGVycywgUkVBU09OQ09ERSA9PSA1NTY4MDAwNikKZHJ1Z19vdmVyZG9zZXMKYGBgCgpHcmVhdCwgbm93IHdlIGp1c3QgbmVlZCB0byBmaWx0ZXIgZm9yIGVuY291bnRlcnMgdGhhdCBvY2N1ciBhZnRlciBKdWx5IDE1LCAxOTk5LgoKVGhlIGBlbmNvdW50ZXJzYCB0YWJsZSBoYXMgdHdvIGNvbHVtbiB0aGF0IHJlcHJlc2VudCB0aGUgZGF0ZSBvZiB0aGUgZW5jb3VudGVyLiBgU1RBUlRgIGFuZCBgU1RPUGAsIGZ1cnRoZXIgY2xhcmlmaWNhdGlvbiB3b3VsZCBiZSBuZWNjZXNzYXJ5IHRvIGRldGVybWluZSBpZiB0aGUgdGFzayBpcyB0byBmaW5kIGVuY291bnRlcnMgdGhhdCBfYmVnaW5fIGFmdGVyIDA3LzE1LzE5OTkgb3IgX2VuZF8gYXQgdGhhdCBkYXRlLiBGb3IgdGhlIHB1cnBvc2VzIG9mIHRoaXMgZXhlcmNpc2UsIHdlJ2xsIGdvIHdpdGggZW5jb3VudGVycyB0aGF0IF9iZWdpbl8gYWZ0ZXIgdGhhdCBkYXRlIGR1ZSB0byB0aGUgdGVybSBfb2NjdXJfIGluIHRoZSBzcGVjaWZpY2F0aW9uLgoKYGBge3J9CmFmdGVyX2RhdGUgPC0gZmlsdGVyKGRydWdfb3ZlcmRvc2VzLCBTVEFSVCA+ICIxOTk5LTA3LTE1IikKYXJyYW5nZShhZnRlcl9kYXRlLCBTVEFSVCkKYGBgCgpOb3cgd2UncmUgY29uY2VybmVkIHdpdGggZW5jb3VudGVycyB3aXRoIHBhdGllbnRzIGJldHdlZW4gdGhlIGFnZXMgb2YgMTggYW5kIDM1OyB3ZSdsbCBuZWVkIHRvIGpvaW4gdGhlIGBwYXRpZW50c2AgdGFibGUgdG8gaGFuZGxlIHRoYXQuCgpgYGB7cn0Kd2l0aF9wYXRpZW50cyA8LSBpbm5lcl9qb2luKGFmdGVyX2RhdGUsIHBhdGllbnRzLCBjKCJQQVRJRU5UIiA9ICJJZCIpKQp3aXRoX3BhdGllbnRzCmBgYAoKQmFzZWQgdXBvbiB0aGUgd29yZGluZyBpbiB0aGUgc3BlY2lmaWNhdGlvbnMsIHRoZSBwYXRpZW50J3MgYWdlIG11c3QgYmUgZ3JlYXRlciB0aGFuIG9yIGVxdWFsIHRvIDE4IGF0IHRoZSBzdGFydCBvZiBhbiBlbmNvdW50ZXIgYW5kIGxlc3MgdGhhbiBvciBlcXVhbCB0byAzNSBhdCB0aGUgZW5kIG9mIHRoZSBlbmNvdW50ZXIuCgpMZXQncyBtYWtlIHN1cmUgdGhhdCB0aGVyZSBhcmUgbm8gZW5jb3VudGVycyBpbiBvdXIgdGFibGUgdGhhdCBoYXMgbm90IGVuZGVkLCBiZWNhdXNlIGEgcGF0aWVudCBjb3VsZCBhZ2UgdG8gMzYgYnkgdGhlIHRpbWUgdGhlIGVuY291bnRlciBpcyBvdmVyLgoKYGBge3J9Cm5vdF9lbmRlZCA8LSBkcm9wX25hKHdpdGhfcGF0aWVudHMsIFNUT1ApCm5vdF9lbmRlZApgYGAKClR1cm5zIG91dCB3ZSdyZSBvay4gTGV0J3MgZG8gdGhlIGZpbHRlcmluZyBub3cuCkZpcnN0IHdlJ2xsIG5lZWQgdG8gY2FsY3VsYXRlIHRoZSBhZ2Ugb2YgdGhlIHBhdGllbnQgYXQgdGhlIHN0YXJ0IGFuZCBlbmQgb2YgdGhlIGVuY291bnRlci4KCmBgYHtyfQpub3RfZW5kZWQkQUdFQVRTVEFSVCA8LSBhcy5wZXJpb2QoaW50ZXJ2YWwobm90X2VuZGVkJEJJUlRIREFURSwgbm90X2VuZGVkJFNUQVJUKSkkeWVhcgpub3RfZW5kZWQkQUdFQVRTVE9QIDwtIGFzLnBlcmlvZChpbnRlcnZhbChub3RfZW5kZWQkQklSVEhEQVRFLCBub3RfZW5kZWQkU1RPUCkpJHllYXIKc2VsZWN0KG5vdF9lbmRlZCwgSWQsIEFHRUFUU1RBUlQsIEFHRUFUU1RPUCkKYGBgCgpgYGB7cn0KYWdlZCA8LSBmaWx0ZXIobm90X2VuZGVkLCBBR0VBVFNUQVJUID49IDE4ICYgQUdFQVRTVE9QIDw9IDM1KQphZ2VkCmBgYAoKVGhhdCBmaW5pc2hlcyB1cCB0aGUgZmlyc3QgdGFzay4=